﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.ComponentModel;
using System.Drawing;
using System.Linq;
using System.Windows.Forms;


namespace Quality
{
    class DB
    {

        public static string connectionString = ConfigurationManager.ConnectionStrings["connection"].ConnectionString;
     
        public static SqlConnection connection = new SqlConnection(connectionString);//U5N7RJ697YCIVZT\MSQLSERVER

        public static SqlConnection CreateSqlCon()
        {
            return new SqlConnection(connectionString);
        }
        //获取数据的记录
        //tablename  数据表
        //str        sql语句
        public static DataSet getlist(SqlDataAdapter da)
        {
           SqlConnection con= DB.CreateSqlCon(); //打开与数据库的连接 
            con.Open();
           
            da.SelectCommand.Connection=con;
        
            DataSet My_DataSet = new DataSet(); //创建DataSet对象 
            da.Fill(My_DataSet);  //通过SqlDataAdapter对象的Fill()方法，将数据表信息添加到DataSet对象中 
            con.Close();
            con.Dispose();//关闭数据库的连接 
            return My_DataSet;  //返回DataSet对象的信息 
        }

     
   

        //执行sql语句返回id
        public static decimal dosqlid(SqlCommand cmd)
        {
            SqlConnection con = DB.CreateSqlCon(); //打开与数据库的连接 
            con.Open(); //打开与数据库的连接 
            cmd.Connection = con;
            decimal re=(decimal)cmd.ExecuteScalar();
            con.Close();
            con.Dispose();//关闭数据库的连接 
            return re;  // 
        }
        //执行sql语句
        public static void dosql(SqlCommand cmd)
        {
            SqlConnection con = DB.CreateSqlCon(); //打开与数据库的连接 
            con.Open(); //打开与数据库的连接 
            cmd.Connection = con;
            cmd.ExecuteNonQuery();
            con.Close();
            con.Dispose();//关闭数据库的连接 
             // 
        }


        //获取combobox的选项
        //com   mbobox控件
        //talbe  数据表
        // name  显示值
        // val   值
        public static void getcombobox(ComboBox com, string table,string val,string name) {

            SqlConnection con = DB.CreateSqlCon(); //打开与数据库的连接 
            con.Open(); 
            string sql = string.Format("select {0},{1} from {2}",val,name,table);
            SqlDataAdapter SQLda = new SqlDataAdapter(sql, con);  //创建一个SqlDataAdapter对象，并获取指定数据表的信息 
            DataSet My_DataSet = new DataSet(); //创建DataSet对象 
            SQLda.Fill(My_DataSet);  //通过SqlDataAdapter对象的Fill()方法，将数据表信息添加到DataSet对象中 
        
            com.DataSource = My_DataSet.Tables[0];
            com.DisplayMember = name;
            com.ValueMember = val;
            com.SelectedIndex = -1;
            con.Close();
            con.Dispose();//关闭数据库的连接 
          

        
        }
        //获取combobox的选项
        //com   mbobox控件
        //talbe  数据表
        // name  显示值
        // val   值
        //filed  查询字段
        //field1  条件
        public static void getcombobox1(ComboBox com, string table, string val, string name,string filed,string filed1)
        {

            SqlConnection con = DB.CreateSqlCon(); //打开与数据库的连接 
            con.Open();
            string sql = string.Format("select {0},{1} from {2} where {3}={4}", val, name, table,filed,filed1);
            SqlDataAdapter SQLda = new SqlDataAdapter(sql, con);  //创建一个SqlDataAdapter对象，并获取指定数据表的信息 
            DataSet My_DataSet = new DataSet(); //创建DataSet对象 
            SQLda.Fill(My_DataSet);  //通过SqlDataAdapter对象的Fill()方法，将数据表信息添加到DataSet对象中 

            com.DataSource = My_DataSet.Tables[0];
            com.DisplayMember = name;
            com.ValueMember = val;
            com.SelectedIndex = -1;
            con.Close();
            con.Dispose();//关闭数据库的连接 



        }

        #region datagridview数据源转化为datatable
        public static DataTable GetDgvToTable(DataGridView dgv, string tablename)
        {
            DataTable dt = new DataTable();
            for (int count = 0; count < dgv.Columns.Count; count++)
            {
                DataColumn dc = new DataColumn(dgv.Columns[count].Name.ToString());
                dt.Columns.Add(dc);
            }
            for (int count = 0; count < dgv.Rows.Count; count++)
            {
                DataRow dr = dt.NewRow();
                for (int countsub = 0; countsub < dgv.Columns.Count; countsub++)
                {
                    dr[countsub] = Convert.ToString(dgv.Rows[count].Cells[countsub].Value);
                }
                dt.Rows.Add(dr);
            }
            dt.TableName = tablename;
            return dt;
        }
        #endregion

        //返回字段中文名称
        public static string getfield( string id) {

            if (id ==null||id=="")
             {return "";
               } 
            string field;
            SqlConnection con = DB.CreateSqlCon(); //打开与数据库的连接 
            con.Open(); 

            SqlDataAdapter SQLda = new SqlDataAdapter("select f_typeitemsname from t_typeselect where f_typeitemsid='" + id + "' ", con);
            DataSet My_DataSet = new DataSet(); //创建DataSet对象 
            int count=SQLda.Fill(My_DataSet);//通过SqlDataAdapter对象的Fill()方法，将数据表信息添加到DataSet对象中 
            if (count > 0)
            {
                field = My_DataSet.Tables[0].Rows[0][0].ToString();
                My_DataSet.Dispose();
                con.Close();
                con.Dispose();//关闭数据库的连接 
                return field;  //返回DataSet对象的信息 
            }
            else 
            { 
                
                field = "";
              
                return field;  //返回DataSet对象的信息 
            }
               
        }
     
        public static void getfielddw2(Control bm, Control text)
        {

            if (string.IsNullOrWhiteSpace(text.Text))
            {
                bm.Text = "";
                text.Text = "";
            }

            SqlConnection con = DB.CreateSqlCon(); //打开与数据库的连接 
            con.Open();

            SqlDataAdapter SQLda = new SqlDataAdapter("select companyCode 企业代码,shortName 简称 from company_BasicInfo where  companyCode ='" + bm.Text + "' ", con);
            DataSet My_DataSet = new DataSet(); //创建DataSet对象 
            int count = SQLda.Fill(My_DataSet);//通过SqlDataAdapter对象的Fill()方法，将数据表信息添加到DataSet对象中 
            if (count > 0)
            {
                bm.Text = My_DataSet.Tables[0].Rows[0][0].ToString();
                text.Text = My_DataSet.Tables[0].Rows[0][1].ToString();
                My_DataSet.Dispose();
                con.Close();
                con.Dispose();//关闭数据库的连接 
            }
            else
            {
                bm.Text = "";
                text.Text = "";

            }


        }
}
    }
